/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ttt.nor2.importdata.txt;

import com.ttt.nor2.util.ExceptionUtil;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.apache.log4j.Logger;

/**
 *
 * @author anuphabm
 */
public class Import4CS extends ImportFile {

    private Logger logger = Logger.getLogger(Import4CS.class);
    private PreparedStatement pstmt;
    private ResultSet rs;

    @Override
    public void savedata() {
        if(rent_month.length != 10)return; // access
        //call function manage month
        try {
            logger.debug("row = " + row);
            logger.debug("find string 'Sum' result : " + line.indexOf("Sum"));
            logger.debug("find string 'Count' result : " + line.indexOf("Count"));
            //manage table Customer//paramerter customer name
            String cust_name = rent_month[4];
            //int cust_id = mtbCustomer(cust_name);
            //manage table phone number //parameter phone_number, rent_id, province_id, cust_id
            String ani = rent_month[2];
            String rent_id = rent_month[3];
            String pv_id = rent_month[0];
            String status = rent_month[1];
            int off_id = mtOffice(rent_month[0].substring(2, 4),rent_month[0] );
            //int phone_id = mtbPhone(ani, rent_id, off_id, cust_id, status);//index current
            //rent_month[5] = getMonth();
            //manage table billing
            //intbBilling(phone_id);//index current
            logger.info(row + ".) Save data sucess.. done");
        } catch (Exception e) {
            logger.error("Exception call fucntion error " + ExceptionUtil.getStackTrace(e));
        } finally {
            row++;
        }// end finally
    }

    private String getMonth() {
        String mon = "";
        if (rent_month[5].length() == 4) {
            mon = "200" + rent_month[5].substring(0, 3);
        } else {
            mon = "20" + rent_month[5].substring(0, 4);
        }
        return mon;
    }

    private void intbBilling(int phone_id) {
        String sql = "";
        try {
            sql = "SELECT Count(p.phone_id) AS c_billing FROM phone AS p " +
                    "Inner Join billing AS b ON p.phone_id = b.phone_id WHERE " +
                    "b.onmonth = ? AND p.phone_num =  ? ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, getMonth());
            pstmt.setString(2, rent_month[2]);
            rs = pstmt.executeQuery();
            int count = -1;
            if (rs.next()) {
                count = rs.getInt("c_billing");
                logger.debug("find data old in tb billing row count : " + count);
            }
            rs.close();
            pstmt.close();

            if (count == 0) {
                sql = "insert into billing (onmonth, rent_value, phone_id)values( ?, ?, ?)";
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, getMonth());
                pstmt.setFloat(2, Float.parseFloat(rent_month[8].replace(",", "")));
                pstmt.setInt(3, phone_id);
                pstmt.executeUpdate();
                pstmt.close();
                logger.info("insert to billing [" + getMonth() + "][" + Float.parseFloat(rent_month[8].replace(",", "")) + "][" + phone_id + "]");

            } else {
                sql = "update billing as b, phone as p set b.status = 'W', rent_value=? where b.phone_id = p.phone_id and b.onmonth = ? and p.phone_num=?";
                try {
                    pstmt = conn.prepareStatement(sql);
                    pstmt.setString(1, "N");
                    pstmt.setFloat(2, Float.parseFloat(rent_month[8].replace(",", "")));
                    pstmt.setString(3, getMonth());
                    pstmt.setString(4, rent_month[2]);
                    int upRowY = pstmt.executeUpdate();
                    logger.info("Update billing [" + rent_month[0] + "] " + upRowY + " recs.");
                    pstmt.close();
                } catch (Exception e) {
                    logger.error("Exception update billing status = Y error " + ExceptionUtil.getStackTrace(e));
                }

            }

        } catch (Exception e) {
            logger.error("Exception  " + ExceptionUtil.getStackTrace(e));
        }

    }
}
